﻿
using System.Data;
using Dapper;
using Microsoft.Extensions.Configuration;
using MySql.Data.MySqlClient;
namespace DataChangeTools.WebApi.Common
{
    public class DapperHelper
    {
        #region 连接
        /// <summary>
        /// 数据库连接串
        /// </summary>
        public static string ConnectionString = string.Empty;

        /// <summary>
        /// 注册一个连接事件
        /// </summary>
        public static IDbConnection? connection = null;

        /// <summary>
        /// 构造函数
        /// </summary>
        public DapperHelper()
        {
            var configuration = new ConfigurationBuilder()
                 .SetBasePath(Directory.GetCurrentDirectory())
                 .AddJsonFile("appsettings.json")
                 .Build();
            ConnectionString = configuration.GetConnectionString("SqlConnetion");

            //ConnectionString = SettingProvider.GetConnection();
            //ConnectionString = ConfigurationManager.ConnectionStrings["SqlConnetion"].ConnectionString;
        }

        /// <summary>
        /// 获得conn对象
        /// </summary>
        /// <returns></returns>
        public IDbConnection GetConn()
        {
            return new MySqlConnection(ConnectionString);
        }

        /// <summary>
        /// 打开conn
        /// </summary>
        /// <param name="conn"></param>
        public void OpenConn(IDbConnection conn)
        {
            conn.Open();
        }

        /// <summary>
        /// 销毁conn
        /// </summary>
        /// <param name="conn"></param>
        public void DisposeConn(IDbConnection conn)
        {
            conn.Dispose();
            conn.Close();
        }
        #endregion

        #region 业务类

        /// <summary>
        /// 查询.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">sql执行语句.</param>
        /// <returns>泛型类.</returns>
        public static List<T> Query<T>(string sql)
             where T : class
        {
            using (connection = new MySqlConnection(ConnectionString))
            {
                return connection.Query<T>(sql).ToList();
            }
        }

        /// <summary>
        /// 查询单条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="obj">new{...}</param>
        /// <returns></returns>
        public T ExecuteQuery<T>(string sql, object? obj = null)
        {
            T result;
            using (connection = new MySqlConnection(ConnectionString))
            {
                result = connection.QueryFirstOrDefault<T>(sql, obj);
            }
            return result;
        }

        /// <summary>
        /// 查询多条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="obj">new{...}</param>
        /// <returns></returns>
        public IEnumerable<T> ExecuteQuerys<T>(string sql, object? obj = null)
        {
            IEnumerable<T> result;
            using (connection = new MySqlConnection(ConnectionString))
            {
                result = connection.Query<T>(sql, obj);
            }
            return result;
        }

        /// <summary>
        /// 查询指定数据.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">sql执行语句.</param>
        /// <param name="t">泛型类.</param>
        /// <returns>类.</returns>
        public static T? Query<T>(string sql, T t)
             where T : class
        {
            using (connection = new MySqlConnection(ConnectionString))
            {
                return connection.Query<T>(sql, t).SingleOrDefault();
            }
        }

        /// <summary>
        /// 查询的in操作.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">sql执行语句.</param>
        /// <returns>泛型类.</returns>
        public static List<T> Query<T>(string sql, int[] ids)
            where T : class
        {
            using (connection = new MySqlConnection(ConnectionString))
            {
                return connection.Query<T>(sql, new { ids }).ToList();
            }
        }

        /// <summary>
        /// 增删改
        /// </summary>
        /// <typeparam name="T">
        /// <param name="sql"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int ExecuteNonQuery<T>(string sql, T obj)
        {
            int result = 0;
            try
            {
                using (connection = new MySqlConnection(ConnectionString))
                {
                    result = connection.Execute(sql, obj);
                }
            }
            catch { }
            return result;
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">sql执行语句.</param>
        /// <param name="t">传入实体类型.</param>
        /// <returns>int.</returns>
        public static int Add<T>(string sql)
            where T : class
        {
            using (connection = new MySqlConnection(ConnectionString))
            {
                return connection.Execute(sql);
            }
        }

        /// <summary>
        /// 新增后获取id
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public static int AddResultId<T>(string sql)
        {
            int Id = 0;
            using (connection = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand command = new MySqlCommand(sql, connection as MySqlConnection))
                {
                    connection.Open();
                    Id = Convert.ToInt32(command.ExecuteScalar());
                    connection.Close();
                }
            }
            return Id;
        }

        /// <summary>
        /// 多语句操作.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">sql执行语句.</param>
        public static void QueryMultiple(string sql)
        {
            using (connection = new MySqlConnection(ConnectionString))
            {
                var multiReader = connection.QueryMultiple(sql);
                multiReader.Dispose();
            }
        }
        #endregion

        #region 事务

        /// <summary>
        /// 开启事务
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public IDbTransaction BeginTransaction(IDbConnection conn)
        {
            return conn.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        /// <param name="tran"></param>
        /// <param name="conn"></param>
        public void Commit(IDbTransaction tran)
        {
            tran.Commit();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        /// <param name="tran"></param>
        /// <param name="conn"></param>
        public void Rollback(IDbTransaction tran)
        {
            tran.Rollback();//回滚
        }

        /// <summary>
        /// 销毁事务
        /// </summary>
        /// <param name="tran"></param>
        /// <param name="conn"></param>
        public void Dispose(IDbTransaction tran)
        {
            tran.Dispose();//销毁
        }

        /// <summary>
        /// 查询带事务
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="conn"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public List<T>? ExecuteQuerys<T>(string sql, IDbTransaction tran)
        {
            try
            {
                return (List<T>)connection.Query(sql, tran);
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// 使用事务查询多条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="obj">new{...}</param>
        /// <returns></returns>
        public IEnumerable<T> ExecuteQuerys<T>(string sql, IDbTransaction tran, IDbConnection conn, object? obj = null)
        {
            IEnumerable<T> result;
            result = conn.Query<T>(sql, obj, tran);
            return result;
        }

        /// <summary>
        /// 使用事务查询单条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="obj">new{...}</param>
        /// <returns></returns>
        public T ExecuteQuery<T>(string sql, IDbTransaction tran, IDbConnection conn, object? obj = null)
        {
            T result;
            result = conn.QueryFirstOrDefault<T>(sql, obj, tran);
            return result;
        }

        /// <summary>
        /// 增删改带事务
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, IDbConnection conn, IDbTransaction tran)
        {
            try
            {
                int count = conn.Execute(sql, transaction: tran);
                return count;
            }
            catch (Exception)
            {
                return 0;
            }
        }

        /// <summary>
        /// 新增后获取id带事务
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="conn"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public int AddResultId<T>(string sql, IDbConnection conn, IDbTransaction tran)
        {
            try
            {
                return Convert.ToInt32(new MySqlCommand(sql, conn as MySqlConnection, tran as MySqlTransaction).ExecuteScalar());
            }
            catch (Exception)
            {
                return 0;
            }
        }
        #endregion

        /// <summary>
        /// 获取表名称
        /// </summary>
        /// <param name="tableName"></param>
        public static string GetTable<T>()
        {
            var tableAttribute = (System.ComponentModel.DataAnnotations.Schema.TableAttribute)typeof(T).GetCustomAttributes(typeof(System.ComponentModel.DataAnnotations.Schema.TableAttribute), true)[0];
            return tableAttribute.Name.ToString();
        }
    }
}
